

'''
-- 创建角色 - 权限关联表
CREATE TABLE role_permissions (
    role_permission_id SERIAL PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    FOREIGN KEY (role_id) REFERENCES roles(role_id),
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id),
    UNIQUE (role_id, permission_id)
);

'''

from . import logger
import psycopg2

class RolePermissionsTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 创建角色 - 权限关联
    def create_role_permission(self, role_id, permission_id):
        try:
            self.cursor.execute("INSERT INTO role_permissions (role_id, permission_id) VALUES (%s, %s)", (role_id, permission_id))
            
        # 唯一键冲突
        except psycopg2.errors.UniqueViolation as e:
            logger.warning(f"Error creating role permission for role_id: {role_id} and permission_id: {permission_id}: {e}")
            return True
        except Exception as e: 
            logger.error(f"Error creating role permission for role_id: {role_id} and permission_id: {permission_id}: {e}")
            return False
        
        else:
            logger.info(f"Role permission created successfully for role_id: {role_id} and permission_id: {permission_id}.")
            return True
        
    # 根据角色ID获取权限 - 权限关联
    def get_role_permissions_by_role_id(self, role_id):
        try:
            self.cursor.execute("SELECT * FROM role_permissions WHERE role_id = %s", (role_id,))
            role_permissions = self.cursor.fetchall()
            logger.info(f"Got role permissions for role_id: {role_id} successfully.")
            return role_permissions
        except Exception as e:
            logger.error(f"Error getting role permissions for role_id: {role_id}: {e}")
            return None

    # 根据权限ID获取角色 - 权限关联
    def get_role_permissions_by_permission_id(self, permission_id):
        try:
            self.cursor.execute("SELECT * FROM role_permissions WHERE permission_id = %s", (permission_id,))
            role_permissions = self.cursor.fetchall()
            logger.info(f"Got role permissions for permission_id: {permission_id} successfully.")
            return role_permissions
        except Exception as e:
            logger.error(f"Error getting role permissions for permission_id: {permission_id}: {e}")
            return None

    # 根据角色ID和权限ID获取角色 - 权限关联
    def get_role_permission_by_role_id_and_permission_id(self, role_id, permission_id):
        try:
            self.cursor.execute("SELECT * FROM role_permissions WHERE role_id = %s AND permission_id = %s", (role_id, permission_id))
            role_permission = self.cursor.fetchone()
            logger.info(f"Got role permission for role_id: {role_id} and permission_id: {permission_id} successfully.")
            return role_permission
        except Exception as e:
            logger.error(f"Error getting role permission for role_id: {role_id} and permission_id: {permission_id}: {e}")
            return None

    # 根据角色ID和权限ID删除 - 权限关联
    def delete_role_permission_by_role_id_and_permission_id(self, role_id, permission_id):
        try:
            self.cursor.execute("DELETE FROM role_permissions WHERE role_id = %s AND permission_id = %s", (role_id, permission_id))
            logger.info(f"Role permission for role_id: {role_id} and permission_id: {permission_id} deleted successfully.")
            return True
        except Exception as e:
            logger.error(f"Error deleting role permission for role_id: {role_id} and permission_id: {permission_id}: {e}")
            return False


    # 根据角色ID 列表获取角色 - 权限关联
    def get_role_permissions_by_role_ids(self, role_ids):
        '''
        role_ids: list of int
        return: list of int
        '''
        if not role_ids:
            return []

        self.cursor.execute("SELECT * FROM role_permissions WHERE role_id IN %s", (tuple(role_ids),))
        role_permissions = self.cursor.fetchall()
        logger.info(f"Got role permissions for role_ids: {role_ids} successfully.")
        return [role_permission["permission_id"] for role_permission in role_permissions]